from APP.common.model import Base, id_key
import sqlalchemy as sa
from sqlalchemy.orm import Mapped, mapped_column,relationship
from APP.utils.timezone import timezone
from sqlalchemy import DateTime
from datetime import datetime
from typing import List

class TbExcel(Base):

    __tablename__ = 'tb_excel'
    
    id: Mapped[id_key] = mapped_column(init=False)
    name:Mapped[str | None] = mapped_column(sa.String(50), nullable=True, comment='excel文件名')
    minio_id: Mapped[int] = mapped_column(sa.Integer(), nullable=False, comment='MinIO存储ID')
    remark: Mapped[str | None] = mapped_column(sa.TEXT(), nullable=True, comment='备注')
    created_time: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), init=False, default_factory=timezone.now, sort_order=999, comment='创建时间'
    )
    # 定义与TbSheet的关系一对多
    sheets:Mapped[List['TbSheet']] = relationship("TbSheet", order_by="TbSheet.id", back_populates="excel")

class TbSheet(Base):
    __tablename__ = 'tb_sheet'
    
    id: Mapped[id_key] = mapped_column(init=False)
    excel_id: Mapped[int] = mapped_column(
        sa.ForeignKey('tb_excel.id', ondelete='CASCADE', onupdate='RESTRICT'), 
        nullable=False
    )
    name: Mapped[str | None] = mapped_column(sa.String(30), nullable=True, comment='sheet表名称')
    order_no: Mapped[int | None] = mapped_column(sa.Integer(), nullable=True, comment='sheet表显示顺序')
    created_time: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), init=False, default_factory=timezone.now, sort_order=999, comment='创建时间'
    )
    
    # 定义与TbExcel的关系一对一
    excel:Mapped['TbExcel'] = relationship("TbExcel", back_populates="sheets")
    # 定义与TbColumn的关系一对多
    columns:Mapped[List['TbColumn']] = relationship("TbColumn", back_populates="sheet")

class TbColumn(Base):
    __tablename__ = 'tb_column'
    
    id: Mapped[id_key] = mapped_column(init=False)
    sheet_id: Mapped[int] = mapped_column(
        sa.ForeignKey('tb_sheet.id', ondelete='CASCADE', onupdate='RESTRICT'),
        nullable=False
    )
    name: Mapped[str | None] = mapped_column(sa.String(50), nullable=True, comment='列名称')
    data_type: Mapped[str | None] = mapped_column(sa.CHAR(1), nullable=True, comment='列值类型0-str；1-int;2-float')
    order_no: Mapped[int | None] = mapped_column(sa.Integer(), nullable=True, comment='列顺序')
    remark: Mapped[str | None] = mapped_column(sa.String(50), nullable=True, comment='备注')
    created_time: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), init=False, default_factory=timezone.now, sort_order=999, comment='创建时间'
    )
    
    # 定义与TbSheet的关系一对一
    sheet:Mapped['TbSheet'] = relationship("TbSheet", back_populates="columns")